iT邦幫忙

第 11 屆 iThome 鐵人賽
DAY 12
1
Software Development

從問題理解與活用SQL語法系列 第 12 篇

第十二堂：家事管理 - 統計每個成員的負責家事次數排名 (Window Function - OVER 子句)

11th鐵人賽
717 瀏覽

  • 分享至 

  • xImage
    •  

一、回顧：第十一堂 情境練習題

題目

刪除不存在於排班資料的實際做家事資料 (CleanRecord)

參考答案

DELETE
FROM cleanrecord
WHERE CleanRecordId IN (
                        SELECT CleanRecordId
                        FROM (
                                SELECT * 
                                FROM CleanRecord
                             ) AS SubQuery_CleanRecord
                        LEFT JOIN CleanSchedule
                        ON SubQuery_CleanRecord.CleanScheduleId = CleanSchedule.CleanScheduleId
                        WHERE CleanSchedule.CleanScheduleId IS NULL
                       )

DELETE VS TRUNCATE Table VS DROP Table

  • DELETE：適合刪除特定的資料
  • Truncate：適合刪除所有的資料並重置識別編號
  • DROP：適合刪除整張表格，刪除後將不存在於資料庫當中

二、第十二堂目標：統計每個成員的負責家事次數排名

需求

泰D想知道排班表當中，知道每個人的工作次數是否平均
今天幫助泰D計算每個人的工作排班次數，並且加上排名

參考實作結果

SELECT  CleanScheduleCount.FamilyName,
        CleanScheduleCount.CountNumber,
        RANK() OVER (ORDER BY CleanScheduleCount.CountNumber DESC) AS Rank
FROM (
      SELECT DISTINCT COUNT(CleanSchedule.CleanScheduleId) OVER (PARTITION BY CleanSchedule.FamilyID) AS CountNumber,
                      Family.FamilyName
      FROM CleanSchedule
      INNER JOIN Family
      ON CleanSchedule.FamilyID = Family.FamilyId
     ) AS CleanScheduleCount

| FamilyName | CountNumber | Rank |
|---------||----------------|--------|
| 泰賢 | 10 | 1 |
| 泰熱 | 10 | 1 |
| 泰肝 | 9 | 3 |
| 泰胖 | 8 | 4 |
| 泰瘦 | 8 | 4 |
| 泰冷 | 7 | 6 |

三、實作思路過程

1.依照成員分類後，計算排班次數

FROM (
      SELECT DISTINCT 
             Family.FamilyName,
             COUNT(CleanSchedule.CleanScheduleId) OVER (PARTITION BY CleanSchedule.FamilyID) AS CountNumber
      FROM CleanSchedule
      INNER JOIN Family
      ON CleanSchedule.FamilyID = Family.FamilyId
     ) AS CleanScheduleCount

在COUNT函數後面使用OVER子句，透過 PARTITION BY，依照「家庭成員(CleanSchedule.FamilyID)」欄位分類後，分別統計每個家庭成員　「CleanSchedule.CleanScheduleId」的出現次數(排班次數)

| FamilyName | CountNumber |
|---------||----------------|
| 泰肝 | 9 |
| 泰熱 | 10 |
| 泰胖 | 8 |
| 泰瘦 | 8 |
| 泰冷 | 7 |
| 泰賢 | 10 |

COUNT(CleanSchedule.CleanScheduleId) OVER (PARTITION BY CleanSchedule.FamilyID) AS CountNumber

2.依據子查詢得到的統計次數，由次數高的到次數低的排名

SELECT  CleanScheduleCount.FamilyName,
        CleanScheduleCount.CountNumber,
        RANK() OVER (ORDER BY CleanScheduleCount.CountNumber DESC) AS Rank
FROM (
      SELECT DISTINCT 
             Family.FamilyName,
             COUNT(CleanSchedule.CleanScheduleId) OVER (PARTITION BY CleanSchedule.FamilyID) AS CountNumber
      FROM CleanSchedule
      INNER JOIN Family
      ON CleanSchedule.FamilyID = Family.FamilyId
     ) AS CleanScheduleCount

| FamilyName | CountNumber | Rank |
|---------||----------------|--------|
| 泰賢 | 10 | 1 |
| 泰熱 | 10 | 1 |
| 泰肝 | 9 | 3 |
| 泰胖 | 8 | 4 |
| 泰瘦 | 8 | 4 |
| 泰冷 | 7 | 6 |

使用Rank()函數，使用OVER子句，透過ORDER BY定義，依照CleanScheduleCount.CountNumber由大到小進行排序，給予排名

RANK() OVER (ORDER BY CleanScheduleCount.CountNumber DESC) AS Rank

四、認識 OVER 子句

在視窗函數處理之前，先將資料進行分類與排序的動作：

  • PARTITON BY：怎麼分類資料(類似第三堂的GROUP BY)
  • ORDER BY：以什麼欄位做為前後順序的排列依據
Window_Function() OVER  ( PARTITON 依照什麼欄位的分類 ORDER BY 以什麼欄位做為前後順序的排列依據 )

五、相關情境練習

修改這一堂的範例，計算「每個人」、「每個分工項目」統計排班的次數，並加上排名

FamilyName ItemName Count_Number Rank
泰賢 打掃 4 1
泰熱 拖地 4 1
泰肝 洗碗 4 1
泰賢 拖地 3 4
泰瘦 倒垃圾 3 4
泰肝 倒垃圾 3 4
泰冷 拖地 3 4
泰瘦 洗碗 3 4
泰胖 洗碗 3 4
泰熱 打掃 3 4
泰熱 倒垃圾 2 1
泰熱 倒垃圾 2 11
泰胖 打掃 2 11
泰胖 倒垃圾 2 11
泰冷 打掃 2 11
泰賢 洗碗 2 11
泰冷 倒垃圾 2 11
泰肝 打掃 1 17
泰熱 洗碗 1 17
泰賢 倒垃圾 1 17
泰瘦 拖地 1 17
泰肝 拖地 1 17
泰瘦 打掃 1 17
泰胖 拖地 1 17
泰冷 洗碗 0 24

六、範本資料DDL

CREATE DATABASE IF NOT EXISTS `ithome2019_familydb`
USE `ithome2019_familydb`;

CREATE TABLE IF NOT EXISTS `cleanitemlist` (
  `CleanItem` int(100) NOT NULL,
  `ItemName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanItem`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=20;


INSERT INTO `cleanitemlist` (`CleanItem`, `ItemName`) VALUES
	(0, '打掃'),
	(1, '拖地'),
	(2, '洗碗'),
	(3, '倒垃圾');

CREATE TABLE IF NOT EXISTS `cleanrecord` (
  `CleanRecordId` varchar(100) NOT NULL,
  `CleanScheduleId` varchar(100) DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `StartTime` datetime DEFAULT NULL,
  `EndTime` datetime DEFAULT NULL,
  PRIMARY KEY (`CleanRecordId`),
  KEY `FK_cleanrecord_family` (`FamilyId`),
  KEY `FK_cleanrecord_cleanschedule` (`CleanScheduleId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=128;


INSERT INTO `cleanrecord` (`CleanRecordId`, `CleanScheduleId`, `FamilyId`, `StartTime`, `EndTime`) VALUES
	('1BBFDF7D-D064-4091-925B-0541C27BD321', '5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-20 18:12:00', '2019-08-20 19:02:00'),
	('38F23254-67F5-4AB5-A8DE-929501AB802E', '150A422A-3A65-4404-9C9A-61A820C13D17', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-23 18:52:00', '2019-08-23 20:19:00'),
	('457B8655-44A1-4F59-8B45-7DB0FD2E1A8E', 'AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-19 18:30:00', '2019-08-19 19:32:00'),
	('4911A9BD-BE90-4174-990B-A72498678A92', '8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-17 18:09:00', '2019-08-17 19:56:00'),
	('49292BD5-27D1-40E8-8A64-73F6A99211C9', '2049F029-0CAA-4249-99C4-5F647B09526D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-20 18:21:00', '2019-08-20 19:12:00'),
	('4A182CCE-4267-4451-A456-3A0B4CD3ED36', '2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-15 18:04:00', '2019-08-15 19:34:00'),
	('4E10D641-A5E9-4FCF-A2EF-5B1896A091D2', '56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-15 18:43:00', '2019-08-15 19:23:00'),
	('5E4206B9-1078-41FF-BF54-A2920A37D843', 'C6727911-38B7-40F8-9C4F-730F652A67D0', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-22 18:59:00', '2019-08-22 20:21:00'),
	('65B732DE-AE07-45E6-8D14-C356E1BDEBE9', '3CC296C1-172B-4EBB-81C0-67B1A849B978', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-14 18:10:00', '2019-08-14 19:40:00'),
	('675FFDEF-1000-4507-87A6-8DAEDD2247BD', 'D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-21 18:01:00', '2019-08-21 19:43:00'),
	('6E2CB5C0-FDD3-4ABA-B38B-BC4915A458EE', '37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-22 18:14:00', '2019-08-22 20:19:00'),
	('7353332C-2E99-48CB-900F-BDE2B7FE39B6', '83B129E4-E37F-4F1F-B013-C2B2CECC8181', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-20 18:29:00', '2019-08-20 19:57:00'),
	('79D7DF92-8A68-4AF1-B5FE-7733EEB495E6', '905AEEEF-2750-4934-9CE5-87416027F09B', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-22 18:44:00', '2019-08-22 19:21:00'),
	('7D4085F9-614D-41FB-AE16-1442E13DEACD', '3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-14 18:31:00', '2019-08-14 19:25:00'),
	('8A284F82-BB85-4B10-A56A-4B07B79A2D80', 'D420FFDD-6BAE-435F-85AE-57D917EA63ED', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-18 18:37:00', '2019-08-18 20:21:00'),
	('8FBCF266-C104-4113-B3DB-461ACAD3048B', '843D27D9-CB11-446B-AF65-563C641D872B', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-16 18:38:00', '2019-08-16 19:40:00'),
	('A7960AAC-6F6D-4B58-94BD-2253253C572D', 'E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-23 18:10:00', '2019-08-23 20:16:00'),
	('A8D86DAE-4135-4090-9D57-04F2D585DD61', '98ADC0FA-508E-4117-8075-4567995A8C45', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-19 18:09:00', '2019-08-19 20:11:00'),
	('ADF35E1E-1760-4012-98CF-CE0B5751C096', 'A25F668E-5E12-4E15-A71D-9ACBC51200A5', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-13 18:09:00', '2019-08-13 19:16:00'),
	('B56F7CC2-EC30-4EB0-9969-5058FDE390AC', 'F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-15 18:15:00', '2019-08-15 19:02:00'),
	('BA54CEF9-55EC-449B-BA32-E48AA84EAFD1', '10DCAA5C-5E44-416D-A758-1846DFA72837', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-20 18:03:00', '2019-08-20 19:51:00'),
	('DAF64DBF-0B83-40CF-A616-D7DD8A538C7C', '9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-21 18:41:00', '2019-08-21 20:19:00'),
	('DB564B2A-F025-43D9-8D27-8E14E717FB4C', '2D682CA3-B900-41B0-9AD9-5611296DFBE5', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-18 18:47:00', '2019-08-18 20:07:00'),
	('DE5FA643-1485-4CC5-B913-82CC5A989929', '9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-14 18:48:00', '2019-08-14 20:12:00'),
	('E5BE6337-DDEB-4107-A5A6-FAD1F3DD4422', 'FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-16 18:52:00', '2019-08-16 19:36:00'),
	('E8A0FFAB-BADD-4DCD-8278-F32BD0C06922', '7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-17 18:45:00', '2019-08-17 19:59:00'),
	('EA538BCF-FE8E-45A8-9BEF-7C08ABBFCB60', '71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-16 18:27:00', '2019-08-16 19:49:00');

CREATE TABLE IF NOT EXISTS `cleanschedule` (
  `CleanScheduleId` varchar(100) NOT NULL,
  `CleanDateTime` datetime DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `CleanItem` int(100) DEFAULT NULL,
  PRIMARY KEY (`CleanScheduleId`),
  KEY `FK_cleanschedule_family` (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=88;


INSERT INTO `cleanschedule` (`CleanScheduleId`, `CleanDateTime`, `FamilyId`, `CleanItem`) VALUES
	('0208B1E1-4F72-4545-8722-E220894526BB', '2019-08-17 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 2),
	('10DCAA5C-5E44-416D-A758-1846DFA72837', '2019-08-20 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', 3),
	('150A422A-3A65-4404-9C9A-61A820C13D17', '2019-08-23 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', 1),
	('2049F029-0CAA-4249-99C4-5F647B09526D', '2019-08-20 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 1),
	('27624528-BFC4-42EB-BC49-ADD6DC58F56D', '2019-08-25 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 2),
	('2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '2019-08-15 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 1),
	('2D682CA3-B900-41B0-9AD9-5611296DFBE5', '2019-08-18 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 2),
	('309570CB-4402-4DD9-85E5-30AC110F819C	2019-08-25 00:00:00.000	34bf1b6f-191d-40e9-9a8c-3c282e6a700d	1', NULL, NULL, NULL),
	('309570CB-4402-4DD9-85E5-30AC110F819C', '2019-08-25 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 1),
	('34931A84-85E7-4236-B1C4-01D190EEFE27', '2019-08-16 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 0),
	('34D5124E-21FE-4A74-BD25-1190B063D0EE', '2019-08-14 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 3),
	('37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '2019-08-22 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 3),
	('3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '2019-08-14 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 2),
	('3CC296C1-172B-4EBB-81C0-67B1A849B978', '2019-08-14 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 1),
	('3DD963A3-FF57-4C3A-83A3-362D533E8B30', '2019-08-24 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 2),
	('3FDF9E95-82EB-4A91-811F-870DA2C0F788', '2019-08-13 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 0),
	('4508A7DA-A570-4918-86DF-591686233A9C', '2019-08-15 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', 3),
	('48BC46E6-72A8-4B16-9BCD-F0711E2AC300', '2019-08-24 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 1),
	('4B4E212B-C40B-4233-B3B3-3531D6FE7915', '2019-08-18 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 1),
	('56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', '2019-08-15 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 0),
	('5B29E024-BB11-4336-ADD7-60443851F599', '2019-08-23 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 0),
	('5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '2019-08-20 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 0),
	('5CFD5FD4-1999-4EBA-B31B-5A2BD1B8C0E5', '2019-08-19 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 3),
	('70AD02FA-9FD7-4AB6-B22C-D3D33F48CD54', '2019-08-13 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 3),
	('71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', '2019-08-16 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 1),
	('7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', '2019-08-17 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', 1),
	('83B129E4-E37F-4F1F-B013-C2B2CECC8181', '2019-08-20 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 2),
	('83F30C22-7B96-4469-A40C-11DA0D5D3C18', '2019-08-25 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 0),
	('843D27D9-CB11-446B-AF65-563C641D872B', '2019-08-16 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 3),
	('8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '2019-08-17 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 3),
	('905AEEEF-2750-4934-9CE5-87416027F09B', '2019-08-22 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 1),
	('98ADC0FA-508E-4117-8075-4567995A8C45', '2019-08-19 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 1),
	('9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '2019-08-21 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 1),
	('9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', '2019-08-14 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 0),
	('A25F668E-5E12-4E15-A71D-9ACBC51200A5', '2019-08-13 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', 1),
	('A47EA3A8-3EAC-4171-92B2-B91B5478C957', '2019-08-21 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 2),
	('ABA175B5-2865-4396-8B0A-39E889A8FAE0', '2019-08-21 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', 0),
	('AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '2019-08-19 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 0),
	('C0F392D2-0461-4589-88A9-7E4C3E24C79E', '2019-08-19 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 2),
	('C23CAA9C-8C63-4F74-8251-1A0773B18104', '2019-08-23 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 2),
	('C6727911-38B7-40F8-9C4F-730F652A67D0', '2019-08-22 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 2),
	('CEC240B4-7988-479F-BEEE-7D39999067B3', '2019-08-17 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 0),
	('D420FFDD-6BAE-435F-85AE-57D917EA63ED', '2019-08-18 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 3),
	('D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '2019-08-21 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 3),
	('DB3C5096-2EF2-445D-89BA-7DE70146C425', '2019-08-24 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', 0),
	('E038F526-9767-4869-A4C5-2E5A795D62F7', '2019-08-25 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 3),
	('E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '2019-08-23 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 3),
	('EB99E74B-ACC4-4C5F-8ECD-9D69A6FB5281', '2019-08-24 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', 3),
	('EF29679A-4952-4F37-AE3F-8F2A14A5A0FF', '2019-08-13 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 2),
	('F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '2019-08-15 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', 2),
	('F802E6A4-5F10-4985-BFD2-0D5C4435EF6A', '2019-08-18 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', 0),
	('FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '2019-08-16 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', 2),
	('FA796E8C-5B52-46DF-8910-F23080B9ECA7', '2019-08-22 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', 0),
	('`', NULL, NULL, NULL);

CREATE TABLE IF NOT EXISTS `family` (
  `FamilyId` varchar(100) NOT NULL,
  `FamilyName` varchar(10) DEFAULT NULL,
  `FamilySex` varchar(10) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=69;


INSERT INTO `family` (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`) VALUES
	('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11 00:00:00', ''),
	('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10 00:00:00', '0934567890'),
	('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13 00:00:00', ''),
	('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10 00:00:00', '0944623456'),
	('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23 00:00:00', '0977654258'),
	('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03 00:00:00', '0944589456');

上一篇
第十一堂：家事管理 - 使用 DELETE 刪除多餘的實際做家事資料 (DELETE vs TRUNCATE vs DROP)
下一篇
第十三堂：合併不同資料來源但結構相同的查詢(UNION 聯集運算)
系列文
從問題理解與活用SQL語法 共 30 篇
  1. 26
    第二十六堂：英文單字測驗程式 - 學生帳號資料管理(5) - 修改學生與刪除學生 (認識實體)
  2. 27
    第二十七堂：英文單字測驗程式 - 學生帳號資料管理(6) - 物件實體操作資料 (ORM、Entity Framework)
  3. 28
    第二十八堂：英文單字測驗程式 - 單字測驗 - 亂數取得單字 (LIMIT、TOP、綜合練習)
  4. 29
    第二十九堂：英文單字測驗程式 - 測驗主明細紀錄與正確率統計 (綜合練習)
  5. 30
    第三十堂：師父引進門，分手在個人 (專案開源+從與SQL的分分合合到現在開課誤人子弟)
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙